/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.database;

import br.com.models.Graduation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Valdigleis
 */
public class DaoTeacherGraduation {
    
    private String sql;
    
    private Connection connection;
    
    /**
     * Método construtor que implementa a classe
     * DaoTeacherGraduation, que é responsavel pela comunicação
     * do sistema com a base de dados na tabela de
     * relacionamento Teacher_Graduation.
     *  
     */
    public DaoTeacherGraduation(){
        this.connection = Derby.getConnection();
    }
    
    /**
     * Método que grava na base de dados a formação do
     * professor.
     * 
     * @param idTeacher
     * @param idGraduation 
     */
    public void saveTeacher_Graduation(int idTeacher, int idGraduation){
        try{
            sql = "INSERT INTO SAD.TEACHER_GRADUATION" +
                " (teacher, graduation)" + " values (?,?)";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, idTeacher);
            stmt.setInt(2, idGraduation);
            stmt.execute();
            stmt.close();
            this.connection.close();
        }catch(SQLException e){
            System.err.println("Erro ao graduação do professor!");
            System.out.println(e.getMessage());
        }
    }
    
    
    public int seacherGraduationOnTeacher(Integer idTeacher){
        try{
            sql = "SELECT * FROM SAD.TEACHER_GRADUATION WHERE teacher = ?";
            PreparedStatement stmt = connection.prepareStatement(sql); 
            stmt.setInt(1, idTeacher);
            ResultSet rs = stmt.executeQuery();
            int i;
            rs.next();
            i = rs.getInt("graduation");
            rs.close(); 
            stmt.close();
            return i;
        }catch(SQLException e){
            System.err.println("Aqui porra!!");
            System.out.println(e.getMessage());
            return 0;
        }
    }
    
    public int seacherGraduationByTeacher(Integer idTeacher){
        try{
            sql = "SELECT SAD.TEACHER_GRADUATION.GRADUATION from SAD.TEACHER_GRADUATION WHERE teacher=?";
            PreparedStatement stmt = connection.prepareStatement(sql); 
            stmt.setInt(1, idTeacher);
            ResultSet rs = stmt.executeQuery();
            int i;
            rs.next();
            i = rs.getInt("graduation");
            rs.close(); 
            stmt.close();
            return i;
        }catch(SQLException e){
            System.err.println("Aqui porra!!");
            System.out.println(e.getMessage());
            return 0;
        }
    }
    
    /**
     * Método que finaliza a conexão da classe TeacherGraduation.
     */
    public void closeConnection(){
        try{
            this.connection.close();
        }catch(SQLException e){
            System.err.println("Erro tentar fechar a conexão da classe Days!");
            System.out.println(e.getMessage());
        }
    }
}
